<?php
/**
 * Created by PhpStorm.
 * User: Leslie
 * Date: 2016/6/23
 * Time: 16:09
 */

namespace app\models;


use app\librarys\Tools;

class Integral
{

    /**
     * 发积分福利总数
     * @param int $startTime
     * @param int $endTime
     * @param $filter
     * @param $institutionId
     * @return mixed
     */
    public static function getWalfareListTotal($startTime = 0, $endTime = 0, $filter, $institutionId, $departmentCode = 0)
    {
        $filterSQL = 'institution_code= :institutionId';

        if ($filter != '0') {
            $filterSQL = $filterSQL . ' and (walfare_name like :filter)';
        }

        if ($startTime != '0') {
            $filterSQL = $filterSQL . ' and (create_date>= :startTime)';
        }
        if ($endTime != '0') {
            $filterSQL = $filterSQL . ' and (SUBSTRING(create_date FROM 1 FOR 10)<= :endTime)';
        }
        if ($departmentCode != '0') {
            $filterSQL = $filterSQL . ' and (department_code like :departmentCode)';
        }
        $sql = "SELECT count(*) AS totalnum,sum(integral) AS totalIntegral  FROM v_institution_walfare WHERE
                 $filterSQL";

        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':institutionId', $institutionId);
        if ($filter != '0') {
            $filter = '%' . $filter . '%';
            $cmd->bindValue(':filter', $filter);
        }
        if ($startTime != '0') {
            $cmd->bindValue(':startTime', $startTime);
        }
        if ($endTime != '0') {
            $cmd->bindValue(':endTime', $endTime);
        }
        if ($departmentCode != '0') {
            $departmentCode = '%' . $departmentCode . '%';
            $cmd->bindValue(':departmentCode', $departmentCode);
        }
        $result = $cmd->queryOne();

        return $result;
    }

    /**
     * 发积分福利列表
     * @param $start
     * @param $pagesize
     * @param int $startTime
     * @param int $endTime
     * @param $filter
     * @param $institutionId
     * @param int $id
     * @return array
     */
    public static function getWalfareList($start, $pagesize, $startTime = 0, $endTime = 0, $filter, $institutionId, $id = 0, $departmentCode = 0)
    {
        if ($id != '0') {
            $filterSQL = 'id=:id';
        } else {
            $filterSQL = 'institution_code= :institutionId';


            if ($filter != '0') {
                $filterSQL = $filterSQL . ' and (walfare_name like :filter)';
            }

            if ($startTime != '0') {
                $filterSQL = $filterSQL . ' and (create_date>= :startTime)';
            }
            if ($endTime != '0') {
                $filterSQL = $filterSQL . ' and (SUBSTRING(create_date FROM 1 FOR 10)<= :endTime)';
            }
            if ($departmentCode != '0') {
                $filterSQL = $filterSQL . ' and (department_code like :departmentCode)';
            }
        }
        $sql = "SELECT * FROM v_institution_walfare WHERE
                 $filterSQL LIMIT $start,$pagesize ";

        $cmd = \Yii::$app->db->createCommand($sql);
        if ($id != '0') {
            $cmd->bindValue(':id', $id);
        } else {
            $cmd->bindValue(':institutionId', $institutionId);


            if ($filter != '0') {
                $filter = '%' . $filter . '%';
                $cmd->bindValue(':filter', $filter);
            }
            if ($startTime != '0') {
                $cmd->bindValue(':startTime', $startTime);
            }
            if ($endTime != '0') {
                $cmd->bindValue(':endTime', $endTime);
            }
            if ($departmentCode != '0') {
                $departmentCode = '%' . $departmentCode . '%';
                $cmd->bindValue(':departmentCode', $departmentCode);
            }
        }
        $result = $cmd->queryAll();

        return $result;

    }

    /**
     * 机构福利
     * @param $institutionId
     * @param $operator_id
     * @param $reason
     * @return false|null|string
     * @throws \yii\db\Exception
     */
    public static function saveInstitutionWalfare($institutionId, $operator_id, $reason, $walfare_type)
    {
        $sql = "call p_save_institution_walfare(:in_institutionId,:in_operator_id,:in_reason,:in_walfare_type,@id)";
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':in_institutionId', $institutionId);
        $cmd->bindValue(':in_operator_id', $operator_id);
        $cmd->bindValue(':in_reason', $reason);
        $cmd->bindValue(':in_walfare_type', $walfare_type);

        $cmd->execute();
        $cmd = \Yii::$app->db->createCommand('SELECT @id');
        $result = $cmd->queryScalar();

        return $result;
    }

    public static function saveDepartmentWalfare($walfareId, $departmentId)
    {
        $sql = "call p_save_department_walfare(:in_walfare_id,:in_department_id)";
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':in_walfare_id', $walfareId);
        $cmd->bindValue(':in_department_id', $departmentId);
        $result = $cmd->queryScalar();

        return $result;
    }

    /**
     * 用户积分增加
     * @param $institutionId
     * @param $send_record_id
     * @param $userId
     * @param $integral
     * @param $reason
     * @return false|null|string
     */
    public static function saveUserIntegral($institutionId, $send_record_id, $userId, $integral, $reason)
    {
        $sql = "call p_save_user_walfare_integral(:in_institutionId,:in_send_record_id,:in_userId,:in_integral,:in_reason)";
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':in_institutionId', $institutionId);
        $cmd->bindValue(':in_send_record_id', $send_record_id);
        $cmd->bindValue(':in_userId', $userId);
        $cmd->bindValue(':in_integral', $integral);
        $cmd->bindValue(':in_reason', $reason);

        $result = $cmd->queryScalar();

        return $result;

    }

    /**
     * 福利用户记录总数
     * @param $filter
     * @param $id
     * @return array|false
     */
    public static function getWalfareUserTotal($filter, $id)
    {
        $filterSQL = 'id=:id';
        if ($filter != '0') {
            $filterSQL = $filterSQL . ' and (name like :filter or phone like :filter)';
        }
        $sql = "SELECT count(*) AS totalnum,sum(integral) AS integral FROM v_institution_walfare_user WHERE $filterSQL";
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':id', $id);
        if ($filter != '0') {
            $filter = '%' . $filter . '%';
            $cmd->bindValue(':filter', $filter);
        }
        $result = $cmd->queryOne();

        return $result;
    }

    /**
     * 福利用户列表
     * @param $start
     * @param $pagesize
     * @param $filter
     * @param $id
     * @return array
     */
    public static function getWalfareUserList($start, $pagesize, $filter, $id)
    {
        $filterSQL = 'id=:id';
        if ($filter != '0') {
            $filterSQL = $filterSQL . ' and (name like :filter or phone like :filter)';
        }
        $sql = "SELECT * FROM v_institution_walfare_user WHERE $filterSQL LIMIT $start,$pagesize";
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':id', $id);
        if ($filter != '0') {
            $filter = '%' . $filter . '%';
            $cmd->bindValue(':filter', $filter);
        }
        $result = $cmd->queryAll();

        return $result;
    }

    /**
     * 获取某个用户的积分记录列表
     * @param int $offset
     * @param int $limit
     * @param string $userId
     * @param int|null $recordType（1：代表收入 2：代表支出）
     * @param string|null $startCreateDate
     * @param string|null $endCreateDate
     * @return array
     */
    public static function getIntegralRecordListByUser($offset, $limit, $userId, $recordType, $startCreateDate, $endCreateDate)
    {
        $filterSQL = '';
        if ($recordType != null && $recordType != '' && $recordType != 0) {
            if ($recordType == 1) {
                $filterSQL = $filterSQL . ' and (record_type=1 or record_type=5)';
            } else {
                $filterSQL = $filterSQL . ' and (record_type=2 or record_type=4)';
            }
        }
        if ($startCreateDate != null && $startCreateDate != '') {
            $filterSQL = $filterSQL . ' and create_datetime >=:start_create_datetime';
        }
        if ($endCreateDate != null && $endCreateDate != '') {
            $filterSQL = $filterSQL . ' and create_datetime <=:end_create_datetime';
        }
        $sql = "SELECT t.id,t.user_id,t.integral,t.record_type,t.origin_id,SUBSTRING(t.create_datetime,1,10) as create_datetime,case when t.remark = '' then case when t.record_type = 2 then '消费' else t.remark end else t.remark end as remark FROM t_user_integral_record t WHERE t.user_id=:user_id $filterSQL ORDER BY create_datetime DESC LIMIT $offset,$limit";
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':user_id', $userId);
        if ($startCreateDate != null && $startCreateDate != '') {
            $cmd->bindValue(':start_create_datetime', $startCreateDate);
        }
        if ($endCreateDate != null && $endCreateDate != '') {
            $cmd->bindValue(':end_create_datetime', $endCreateDate);
        }
        $items = $cmd->queryAll();

        return $items;
    }

    /**
     * 获取某个用户的积分记录数量
     * @param string $userId
     * @param string|null $recordType
     * @param string|null $startCreateDate
     * @param string|null $endCreateDate
     * @return false|null|string
     */
    public static function getIntegralRecordCountByUser($userId, $recordType, $startCreateDate, $endCreateDate)
    {
        $filterSQL = '';
        if ($recordType != null && $recordType != '' && $recordType != 0) {
            if ($recordType == 1) {
                $filterSQL = $filterSQL . ' and (record_type=1 or record_type=4)';
            } else {
                $filterSQL = $filterSQL . ' and (record_type=2 or record_type=5)';
            }
        }
        if ($startCreateDate != null && $startCreateDate != '') {
            $filterSQL = $filterSQL . ' and create_datetime >=:start_create_datetime';
        }
        if ($endCreateDate != null && $endCreateDate != '') {
            $filterSQL = $filterSQL . ' and create_datetime <=:end_create_datetime';
        }
        $sql = "SELECT COUNT(t.id) FROM t_user_integral_record t WHERE t.user_id=:user_id $filterSQL ";
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':user_id', $userId);
        if ($startCreateDate != null && $startCreateDate != '') {
            $cmd->bindValue(':start_create_datetime', $startCreateDate);
        }
        if ($endCreateDate != null && $endCreateDate != '') {
            $cmd->bindValue(':end_create_datetime', $endCreateDate);
        }

        $item = $cmd->queryScalar();

        return $item;
    }

    /**
     * 获取某个用户(半年内的收入或者支出)积分总合
     * @param string $userId
     * @param string|null $recordType
     * @return false|null|string
     */
    public static function getIntegralTotalByUser($userId, $recordType)
    {
        $filterSQL = '';
        if ($recordType != null && $recordType != '' && $recordType != 0) {
            $filterSQL = $filterSQL . ' and record_type=:record_type';
        }
        $sql = "SELECT sum(t.integral) FROM t_user_integral_record t WHERE SUBSTRING(create_datetime FROM 1 FOR 10)>=CONCAT(SUBSTRING(date_add(NOW(), interval -6 month) FROM 1 FOR 7),'-01') and t.user_id=:user_id $filterSQL ";
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':user_id', $userId);
        if ($recordType != null && $recordType != '' && $recordType != 0) {
            $cmd->bindValue(':record_type', $recordType);
        }
        $item = $cmd->queryScalar();

        return $item;
    }

    public static function getUserIntegral($userId)
    {
        $sql = "select integral from t_user where id=:user_id ";
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':user_id', $userId);
        $item = $cmd->queryScalar();
        return $item;
    }

    /**
     * @param $data
     * @return int
     * @throws \Exception
     * @throws \yii\db\Exception
     */
    public static function sendUserIntegral($data)
    {
        $status = 1;

        $transaction = \Yii::$app->db->beginTransaction();
        try {
            $send_record_id = self::saveInstitutionWalfare($data['institutionCode'], null, 'reason', 1);
            if ($send_record_id == '0') {
                throw new \Exception('写入机构福利失败！');
            } else {
                $result = self::saveUserIntegral($data['institutionCode'], $send_record_id, $data['user_id'], $data['integral'], 'reason');
                if ($result) {
                    throw new \Exception('发放积分失败！');
                }

                $transaction->commit();
                $status = 0;
            }
        } catch (\Exception $e) {
            $transaction->rollBack();
            throw $e;
        }
        return $status;
    }

    /**
     * 写入机构发分事件及添加用户积分
     * @param $data
     * @return int
     * @throws \Exception
     * @throws \yii\db\Exception
     */
    public static function addInstituonUserIntegral($data)
    {
        $status = 1;
        $message = "";
        //生成发放记录
        $transaction = \Yii::$app->db->beginTransaction();
        try {
            $userintegrals = $data['userintegrals'];
            $send_record_id = self::saveInstitutionWalfare($data['institutionId'], $data['operator_id'], $data['reason'], $data['walfare_type']);
            if ($send_record_id == '0') {
                throw new \Exception('写入机构福利失败！');
            } else {

                if ($data['departmentId'] != null && $data['departmentId'] != '' && $data['departmentId'] != '0') {
                    self::saveDepartmentWalfare($send_record_id, $data['departmentId']);
                }
                foreach ($userintegrals as $record) {
                    $result = self::saveUserIntegral($data['institutionId'], $send_record_id, $record['id'], $record['integral'], $data['reason']);

                    if ($result) {

                        $message = $record['username'] . '发放积分失败！';
                        throw new \Exception($message);
                    }
                }
                $transaction->commit();
                $status = 0;
            }
        } catch (\Exception $e) {
            $transaction->rollBack();
            throw $e;
            $status = 1;
        }
        return $status;
    }

    /**新增机构福利预算
     * @param $institution_code
     * @param $department_id
     * @param $name
     * @param $year
     * @param $year_budget
     * @param $is_month_budget
     * @param $create_manager_id
     * @param $month_budget
     * @return int
     * @throws \yii\db\Exception
     */
    public static function addInstituonWalfareBudget($institution_code, $department_id, $name, $year, $year_budget, $is_month_budget, $create_manager_id, $month_budget)
    {
        $sql = "INSERT INTO t_institution_walfare_budget
          (id,institution_code,department_id,name,year,year_budget,is_month_budget,create_manager_id,create_datetime
          ,month1_budget,month2_budget,month3_budget,month4_budget,month5_budget,month6_budget
          ,month7_budget,month8_budget,month9_budget,month10_budget,month11_budget,month12_budget)
          VALUES
          (:id,:institution_code,:department_id,:name,:year,:year_budget,:is_month_budget,:create_manager_id,now()
          ,:month1_budget,:month2_budget,:month3_budget,:month4_budget,:month5_budget,:month6_budget
          ,:month7_budget,:month8_budget,:month9_budget,:month10_budget,:month11_budget,:month12_budget)";
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':id', Tools::db_uuid());
        $cmd->bindValue(':institution_code', $institution_code);
        $cmd->bindValue(':department_id', $department_id);
        $cmd->bindValue(':name', $name);
        $cmd->bindValue(':year', $year);
        $cmd->bindValue(':year_budget', $year_budget);
        $cmd->bindValue(':is_month_budget', $is_month_budget);
        $cmd->bindValue(':create_manager_id', $create_manager_id);
        for ($i = 1; $i <= 12; $i++) {
            $cmd->bindValue(":month{$i}_budget", $month_budget[$i]);
        }
        return $cmd->execute();
    }

    /**修改机构福利预算
     * @param $id
     * @param $name
     * @param $year
     * @param $year_budget
     * @param $is_month_budget
     * @param $modify_manager_id
     * @param $month_budget
     * @return int
     * @throws \yii\db\Exception
     */
    public static function updateInstituonWalfareBudget($id, $name, $year, $year_budget, $is_month_budget, $modify_manager_id, $month_budget)
    {
        $sql = "UPDATE t_institution_walfare_budget SET
           name=:name,year=:year,year_budget=:year_budget,is_month_budget=:is_month_budget,modify_manager_id=:modify_manager_id,modify_datetime=:now()
          ,month1_budget=:month1_budget,month2_budget=:month2_budget,month3_budget=:month3_budget,month4_budget=:month4_budget,month5_budget=:month5_budget,month6_budget=:month6_budget
          ,month7_budget=:month7_budget,month8_budget=:month8_budget,month9_budget=:month9_budget,month10_budget=:month10_budget,month11_budget=:month11_budget,month12_budget=:month12_budget
           where id=:id";
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':id', $id);
        $cmd->bindValue(':name', $name);
        $cmd->bindValue(':year', $year);
        $cmd->bindValue(':year_budget', $year_budget);
        $cmd->bindValue(':is_month_budget', $is_month_budget);
        $cmd->bindValue(':modify_manager_id', $modify_manager_id);
        for ($i = 1; $i <= 12; $i++) {
            $cmd->bindValue(":month{$i}_budget", $month_budget[$i]);
        }
        return $cmd->execute();
    }

}